package cn.campsg.messageboard.dao;

import cn.campsg.messageboard.idao.IMessageDao;
import cn.campsg.messageboard.model.Message;
import cn.campsg.messageboard.model.User;
import cn.campsg.messageboard.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MessageDaoImpl implements IMessageDao {

    @Override
    public void addMessage(Message message) {
        String sql = "INSERT INTO message (user_id, content, created_at, parent_id) VALUES (?, ?, NOW(), ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, message.getUserId());
            pstmt.setString(2, message.getContent());
            if (message.getParentId() == null) {
                pstmt.setNull(3, java.sql.Types.INTEGER);
            } else {
                pstmt.setInt(3, message.getParentId());
            }
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Message> getMessagesByUserId(int userId) {
        List<Message> messages = new ArrayList<>();
        String sql = "SELECT * FROM message WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Message msg = new Message();
                // 根据你的Message类属性设置
                msg.setId(rs.getInt("id"));
                msg.setUserId(rs.getInt("user_id"));
                msg.setContent(rs.getString("content"));
                msg.setCreatedAt(rs.getTimestamp("created_at"));
                msg.setParentId(rs.getInt("parent_id"));
                msg.setLikeCount(rs.getInt("like_count"));
                msg.setStatus(rs.getInt("status"));
                messages.add(msg);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    @Override
    public Message getMessageById(int id) {
        Message message = null;
        String sql = "SELECT * FROM message WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                message = new Message();
                message.setId(rs.getInt("id"));
                message.setUserId(rs.getInt("user_id"));
                message.setContent(rs.getString("content"));
                message.setCreatedAt(rs.getTimestamp("created_at"));
                message.setParentId(rs.getInt("parent_id"));
                message.setLikeCount(rs.getInt("like_count"));
                message.setStatus(rs.getInt("status"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return message;
    }

//    @Override
//    public List<Message> getReplies(int messageId) {
//        List<Message> replies = new ArrayList<>();
//        String sql = "SELECT * FROM message WHERE parent_id = ?";
//        try (Connection conn = DBUtil.getConnection();
//             PreparedStatement pstmt = conn.prepareStatement(sql)) {
//            pstmt.setInt(1, messageId);
//            ResultSet rs = pstmt.executeQuery();
//            while (rs.next()) {
//                Message msg = new Message();
//                // 填充msg属性
//                replies.add(msg);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return replies;
//    }

    @Override
    public List<Message> searchMessages(String keyword, int offset, int limit) {
        // 只查询内容包含关键字的留言（parent_id=0表示顶级留言）
        String sql = "SELECT m.*, u.username, u.avatar FROM message m " +
                "JOIN user u ON m.user_id = u.id " +
                "WHERE m.content LIKE ? AND (m.parent_id IS NULL OR m.parent_id = 0) " +
                "ORDER BY m.created_at DESC LIMIT ?, ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, "%" + keyword + "%"); // 包含关键字
            pstmt.setInt(2, offset);
            pstmt.setInt(3, limit);

            ResultSet rs = pstmt.executeQuery();
            List<Message> messages = new ArrayList<>();
            while (rs.next()) {
                Message message = new Message();
                // 基础字段
                message.setId(rs.getInt("id"));
                message.setUserId(rs.getInt("user_id"));
                message.setContent(rs.getString("content")); // 原始内容
                message.setCreatedAt(rs.getTimestamp("created_at"));
                message.setLikeCount(rs.getInt("like_count"));

                // 关联用户信息
                User user = new User();
                user.setId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setAvatar(rs.getString("avatar"));
                message.setUser(user);

                // 获取该留言的回复（且回复内容也必须包含关键字）
                message.setReplies(searchReplies(keyword, message.getId()));

                messages.add(message);
            }
            return messages;

        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    // 新增方法：搜索包含关键字的回复
    private List<Message> searchReplies(String keyword, int parentId) {
        String sql = "SELECT m.*, u.username, u.avatar FROM message m " +
                "JOIN user u ON m.user_id = u.id " +
                "WHERE m.content LIKE ? AND m.parent_id = ? " +
                "ORDER BY m.created_at ASC"; // 回复按时间正序排列

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setInt(2, parentId);

            ResultSet rs = pstmt.executeQuery();
            List<Message> replies = new ArrayList<>();
            while (rs.next()) {
                Message reply = new Message();
                reply.setId(rs.getInt("id"));
                reply.setUserId(rs.getInt("user_id"));
                reply.setContent(rs.getString("content"));
                reply.setCreatedAt(rs.getTimestamp("created_at"));
                reply.setLikeCount(rs.getInt("like_count"));

                User user = new User();
                user.setId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setAvatar(rs.getString("avatar"));
                reply.setUser(user);

                replies.add(reply);
            }
            return replies;

        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }


    //    @Override
//    public List<Message> getAllMessages(int i, int recordsPerPage) {
//        String sql = "SELECT * FROM message ORDER BY created_at DESC LIMIT ?, ?";
//        try (Connection conn = DBUtil.getConnection();
//             PreparedStatement pstmt = conn.prepareStatement(sql)) {
//            pstmt.setInt(1, i);
//            pstmt.setInt(2, recordsPerPage);
//            ResultSet rs = pstmt.executeQuery();
//            List<Message> messages = new ArrayList<>();
//            while (rs.next()) {
//                Message message = new Message();
//                message.setId(rs.getInt("id"));
//                message.setUserId(rs.getInt("user_id"));
//                message.setContent(rs.getString("content"));
//                message.setCreatedAt(rs.getTimestamp("created_at"));
//                message.setParentId(rs.getObject("parent_id", Integer.class));
//                message.setLikeCount(rs.getInt("like_count"));
//                messages.add(message);
//            }
//            return messages;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return List.of();
//    }
    @Override
    public List<Message> getAllMessages(int offset, int limit) {
        List<Message> messages = new ArrayList<>();
        String sql = "SELECT * FROM message WHERE parent_id IS NULL OR parent_id = 0 ORDER BY created_at DESC LIMIT ?, ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, offset);
            pstmt.setInt(2, limit);
            ResultSet rs = pstmt.executeQuery();
            UserDao userDao = new UserDao();
            while (rs.next()) {
                Message message = new Message();
                int userId = rs.getInt("user_id");
                message.setId(rs.getInt("id"));
                message.setUserId(userId);
                message.setContent(rs.getString("content"));
                message.setCreatedAt(rs.getTimestamp("created_at"));
                message.setParentId(rs.getObject("parent_id", Integer.class));
                message.setLikeCount(rs.getInt("like_count"));
                // 查留言用户信息
                message.setUser(userDao.getUserById(userId));
                // 查replies
                List<Message> replies = getReplies(message.getId());
                message.setReplies(replies);
                messages.add(message);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    public List<Message> getReplies(int messageId) {
        List<Message> replies = new ArrayList<>();
        String sql = "SELECT * FROM message WHERE parent_id = ? ORDER BY created_at ASC";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, messageId);
            ResultSet rs = pstmt.executeQuery();
            UserDao userDao = new UserDao();
            while (rs.next()) {
                Message reply = new Message();
                reply.setId(rs.getInt("id"));
                int userId = rs.getInt("user_id");
                reply.setUserId(userId);
                reply.setContent(rs.getString("content"));
                reply.setCreatedAt(rs.getTimestamp("created_at"));
                reply.setParentId(rs.getObject("parent_id", Integer.class));
                reply.setLikeCount(rs.getInt("like_count"));
                // 查出回复者信息
                reply.setUser(userDao.getUserById(userId));
                // 递归查找该回复的子回复（如支持多级回复，否则可省略）
                reply.setReplies(getReplies(reply.getId()));
                replies.add(reply);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return replies;
    }

    @Override
    public int getTotalCount() {
        String sql = "SELECT COUNT(*) FROM message";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public void addMessage(int userId, String content) {
        String sql = "INSERT INTO message (user_id, content, created_at) VALUES (?, ?, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            pstmt.setString(2, content);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public int getSearchCount(String keyword) {
        String sql = "SELECT COUNT(*) FROM message WHERE content LIKE ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "%" + keyword + "%");
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public void addReply(int userId, String content, int parentId) {
        String sql = "INSERT INTO message (user_id, content, created_at, parent_id) VALUES (?, ?, NOW(), ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            pstmt.setString(2, content);
            pstmt.setInt(3, parentId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteMessage(int messageId) {
        String sql = "DELETE FROM message WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, messageId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Message> getAllMessages() {
        List<Message> messages = new ArrayList<>();
        String sql = "SELECT * FROM message ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Message message = new Message();
                message.setId(rs.getInt("id"));
                message.setUserId(rs.getInt("user_id"));
                message.setContent(rs.getString("content"));
                message.setCreatedAt(rs.getTimestamp("created_at"));
                message.setParentId(rs.getObject("parent_id", Integer.class));
                message.setLikeCount(rs.getInt("like_count"));
                messages.add(message);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }


    public void deleteMessage(int messageId, int userId) {
        String sql = "DELETE FROM message WHERE id = ? AND user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, messageId);
            pstmt.setInt(2, userId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateMessage(Message message) {
        String sql = "UPDATE message SET content = ? WHERE id = ? AND user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, message.getContent());
            pstmt.setInt(2, message.getId());
            pstmt.setInt(3, message.getUserId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public void likeMessage(int messageId) {
        String sql = "UPDATE message SET like_count = like_count + 1 WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, messageId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public void unlikeMessage(int messageId) {
        String sql = "UPDATE message SET like_count = like_count - 1 WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, messageId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}